<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\PriDeviceInfo;
use DB;
use Carbon\Carbon;
use Cache;

class GenerateAllHour extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'generate:allhour {date?* : input generate data} {--all}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'generate by all hour data';
    
    protected $cacheKeys = [];

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $data = $this->getPrevHourData();
        $this->saveData($data);
    }
    
    //获取上一小时数据
    protected function getPrevHourData() {

        $isAll = $this->option('all');

        if($isAll){
            $where="1";
            $params = [];
        }else{
            $inputDate = $this->argument('date');
            if(count($inputDate)==1) {
               $start = $inputDate[0];
               $end = $start;
            }else {
                list($start, $end) = $inputDate;
            }
            $startDate = Carbon::parse($start)->startOfDay()->toDateTimeString();
            $endDate = Carbon::parse($end)->endOfDay()->toDateTimeString();
            $where='hd_datetime between ? and ? ';
            $params = [$startDate, $endDate];
        }

        $sql=<<<EOT
        select 
            pdi_index,
            date_format(hd_datetime, '%Y-%m-%d %H') as hours,
            round(sum(hd_InTurb)/count(hd_InTurb),2) as hd_InTurb,
            round(sum(hd_PH)/count(hd_PH),2) as hd_PH,
            round(sum(hd_OutTurb)/count(hd_OutTurb),2) as hd_OutTurb,
            round(sum(hd_ResChlo)/count(hd_ResChlo),2) as hd_ResChlo,
            round(sum(hd_Level)/count(hd_Level),2) as hd_Level,
            round(sum(hd_Pressure)/count(hd_Pressure),2) as hd_Pressure,
            round(sum(hd_Flow)/count(hd_Flow),2) as hd_Flow,
            max(hd_AllFlow) as hd_AllFlow
            from t_hisdata_WaterAff
            where $where
            group by pdi_index, hours
            order by hours
EOT;
        $data = DB::select($sql, $params);
        if(!$data) {
            $this->info("没有查询到数据 {$inputDate}");
            return [];
        }
        return $data;
    }
    
    //保存数据
    protected function saveData($data) {
        if(!$data){
            return false;
        }
        //AareId 站点 Co_ID 公司
        $device = PriDeviceInfo::select(['pdi_index', 'Co_ID', 'AreaId'])
                ->with(['area' => function($query) {
                    $query->select(['id', 'name']);
                }])
                ->get()
                ->keyBy('pdi_index')
                ->toArray();
        $result = [];
        $result1 = [];
        $groupResult = collect($data)->groupBy(function ($item) {
            return explode(' ', $item->hours)[0];
        });
        foreach ($groupResult as $vv) {
            $result = [];
            foreach ($vv as  $v) {
                $datesArr = explode(" ", $v->hours);
                $deviceInfo = array_get($device, $v->pdi_index);
                $temp = [
                    'pdi_index'=>$v->pdi_index,
                    'company_id'=>array_get($deviceInfo, 'Co_ID'),
                    'site_id'=>array_get($deviceInfo, 'AreaId'),
                    'name'=>array_get($deviceInfo, 'area.name'),
                    'at_date'=>$datesArr[0],
                    'InTurb'=>$v->hd_InTurb,
                    'PH'=>$v->hd_PH,
                    'OutTurb'=>$v->hd_OutTurb,
                    'ResChlo'=>$v->hd_ResChlo,
                    'Level'=>$v->hd_Level,
                    'Pressure'=>$v->hd_Pressure,
                    'Flow'=>$v->hd_Flow,
                    'AllFlow'=>$v->hd_AllFlow,
                ];
                $temp['AllFlow'] = $this->getAllflow($temp);
                $result[] = array_merge($temp, [ 'hour'=>$datesArr[1], 'allflow_total'=>$v->hd_AllFlow ]);
                $result1[] = $temp;
            }
            DB::table('t_wateraff_hour')->insert($result);
        }
        
        $this->batchInsert($result1);
        $this->clearCache();
    }
    
    //累积供水量
    protected function getAllflow($v) {
        if($this->isLeigong($v['name'])) {
            $yes = $this->getYesterdayLastHourData($v['at_date'], $v['pdi_index']);
            $this->info($yes);
            return $v['AllFlow']-$yes;
        }
        return $v['AllFlow'];
    }
    
    //是否雷公水厂
    protected function isLeigong($name) {
        return starts_with($name, '雷公');
    }
    //雷公水厂特殊处理, 获取昨天最后一次数据
    protected function getYesterdayLastHourData($date, $pdiIndex) {
        $yesterday = Carbon::parse($date)->subDay()->toDateString();
        $cacgeKey = 'command_day_hour_'.$yesterday.'_'.$pdiIndex;
        $cacheValue = Cache::get($cacgeKey);
        $this->info($cacheValue.'_'.$pdiIndex.'_'.$date.'_'.$yesterday);
        if($cacheValue!==null){
            return $cacheValue;
        }

        $info = DB::table('t_wateraff_hour')
            ->where('at_date', $yesterday)
            ->where('pdi_index', $pdiIndex)
            ->select(['allflow_total'])
            ->orderBy('hour', 'desc')
            ->first();
        $retVal = data_get($info, 'allflow_total', 0);
        Cache::put($cacgeKey, $retVal, now()->endOfDay());
        $this->cacheKeys[] = $cacgeKey;
        return $retVal;
    }
    
    protected function clearCache() {
        foreach ($this->cacheKeys as $key) {
            Cache::forget($key);
        }
    }
    
    //格式化更新字段
    protected function formatUpdateFields($v) {
        if($v=='AllFlow') {
            return "{$v}=values({$v})";
        }
        return "{$v}={$v}+values({$v})";
    }
    
    //保存monthData
    protected function batchInsert($data, $table="t_wateraff_month", $isCustom=false,$upFields=[]) {
        $first = array_keys($data[0]);
        $first[]='num';
        $fields = "`".implode('`,`', $first)."`";
        $values="";
        $sql="insert into {$table} ({$fields}) values ";
        foreach ($data as $v) {
            $v['num']=1;
            $values .= ($values?',':'')."('".implode("','", array_values($v))."')";
        }
        $updates = '';
        if(!$isCustom) {
            $updateFields = $upFields?:config('water.waterFields');

            foreach ($updateFields as $v) {
                $updates .= ($updates?',':'').$this->formatUpdateFields($v);
            }
            
            $updates .=", num=num+1";
            
        }else{
            $updates = $isCustom;
        }

        DB::insert($sql.$values." ON DUPLICATE KEY UPDATE {$updates};");
    }

}
